#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
创建系统字典表迁移脚本
用于统一管理所有选项数据，替代前端硬编码
"""

import sqlite3
import os
from datetime import datetime

def get_db_path():
    """获取数据库路径"""
    current_dir = os.path.dirname(os.path.abspath(__file__))
    backend_dir = os.path.dirname(current_dir)
    db_path = os.path.join(backend_dir, 'instance', 'dispatch_system.db')
    return db_path

def create_system_dictionaries_table():
    """创建系统字典表"""
    db_path = get_db_path()
    
    if not os.path.exists(db_path):
        print(f"数据库文件不存在: {db_path}")
        return False
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # 检查表是否已存在
        cursor.execute("""
            SELECT name FROM sqlite_master 
            WHERE type='table' AND name='system_dictionaries'
        """)
        
        if cursor.fetchone():
            print("system_dictionaries 表已存在，跳过创建")
            return True
        
        # 创建系统字典表
        cursor.execute("""
            CREATE TABLE system_dictionaries (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                dict_type VARCHAR(50) NOT NULL,           -- 字典类型
                dict_key VARCHAR(50) NOT NULL,            -- 字典键值
                dict_label VARCHAR(100) NOT NULL,         -- 显示标签
                dict_value VARCHAR(100),                  -- 字典值(可选)
                sort_order INTEGER DEFAULT 0,            -- 排序
                is_active BOOLEAN DEFAULT TRUE,          -- 是否启用
                is_system BOOLEAN DEFAULT FALSE,         -- 是否系统内置
                parent_id INTEGER,                       -- 父级ID
                description TEXT,                        -- 描述
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(dict_type, dict_key)
            )
        """)
        
        print("✅ system_dictionaries 表创建成功")
        
        # 插入初始化数据
        insert_initial_data(cursor)
        
        conn.commit()
        conn.close()
        
        print("✅ 系统字典表创建和初始化完成")
        return True
        
    except Exception as e:
        print(f"❌ 创建系统字典表失败: {str(e)}")
        if 'conn' in locals():
            conn.rollback()
            conn.close()
        return False

def insert_initial_data(cursor):
    """插入初始化数据"""
    
    # 工单类型数据
    work_order_types = [
        ('work_order_type', 'maintenance', '维修', None, 1, True, True, None, '设备维修工单'),
        ('work_order_type', 'cleaning', '清洁', None, 2, True, True, None, '清洁工单'),
        ('work_order_type', 'safety', '安全检查', None, 3, True, True, None, '安全检查工单'),
        ('work_order_type', 'inspection', '设备巡检', None, 4, True, True, None, '设备巡检工单'),
        ('work_order_type', 'other', '其他', None, 5, True, True, None, '其他类型工单')
    ]
    
    # 优先级数据
    priority_levels = [
        ('priority', 'low', '低', None, 1, True, True, None, '低优先级'),
        ('priority', 'medium', '中', None, 2, True, True, None, '中等优先级'),
        ('priority', 'high', '高', None, 3, True, True, None, '高优先级'),
        ('priority', 'urgent', '紧急', None, 4, True, True, None, '紧急优先级')
    ]
    
    # 工单状态数据
    work_order_status = [
        ('work_order_status', 'pending', '待处理', None, 1, True, True, None, '工单待处理状态'),
        ('work_order_status', 'assigned', '已分配', None, 2, True, True, None, '工单已分配状态'),
        ('work_order_status', 'in_progress', '处理中', None, 3, True, True, None, '工单处理中状态'),
        ('work_order_status', 'completed', '已完成', None, 4, True, True, None, '工单已完成状态'),
        ('work_order_status', 'cancelled', '已取消', None, 5, True, True, None, '工单已取消状态')
    ]
    
    # 材料分类数据
    material_categories = [
        ('material_category', 'electronic', '电子元件', None, 1, True, True, None, '电子元件类材料'),
        ('material_category', 'mechanical', '机械配件', None, 2, True, True, None, '机械配件类材料'),
        ('material_category', 'office', '办公用品', None, 3, True, True, None, '办公用品类材料'),
        ('material_category', 'cleaning', '清洁用品', None, 4, True, True, None, '清洁用品类材料'),
        ('material_category', 'other', '其他', None, 5, True, True, None, '其他类材料')
    ]
    
    # 库存状态数据
    stock_status = [
        ('stock_status', 'sufficient', '充足', None, 1, True, True, None, '库存充足'),
        ('stock_status', 'insufficient', '不足', None, 2, True, True, None, '库存不足'),
        ('stock_status', 'out_of_stock', '缺货', None, 3, True, True, None, '库存缺货')
    ]
    
    # 材料使用类型数据
    usage_types = [
        ('usage_type', 'work_order', '工单使用', None, 1, True, True, None, '工单相关使用'),
        ('usage_type', 'maintenance', '维护使用', None, 2, True, True, None, '维护相关使用'),
        ('usage_type', 'other', '其他使用', None, 3, True, True, None, '其他用途使用')
    ]
    
    # 用户角色数据
    user_roles = [
        ('user_role', 'admin', '管理员', None, 1, True, True, None, '系统管理员'),
        ('user_role', 'manager', '经理', None, 2, True, True, None, '部门经理'),
        ('user_role', 'user', '用户', None, 3, True, True, None, '普通用户')
    ]
    
    # 用户状态数据
    user_status = [
        ('user_status', 'active', '在职', None, 1, True, True, None, '在职状态'),
        ('user_status', 'inactive', '离职', None, 2, True, True, None, '离职状态')
    ]
    
    # 性别数据
    gender_options = [
        ('gender', 'male', '男', None, 1, True, True, None, '男性'),
        ('gender', 'female', '女', None, 2, True, True, None, '女性')
    ]
    
    # 障碍原因数据
    failure_reasons = [
        ('failure_reason', 'equipment_failure', '设备故障', None, 1, True, True, None, '设备故障原因'),
        ('failure_reason', 'circuit_issue', '电路问题', None, 2, True, True, None, '电路问题原因'),
        ('failure_reason', 'software_issue', '软件故障', None, 3, True, True, None, '软件故障原因'),
        ('failure_reason', 'human_damage', '人为损坏', None, 4, True, True, None, '人为损坏原因'),
        ('failure_reason', 'natural_wear', '自然损耗', None, 5, True, True, None, '自然损耗原因'),
        ('failure_reason', 'other', '其他', None, 6, True, True, None, '其他原因')
    ]
    
    # 满意度评级数据
    satisfaction_levels = [
        ('satisfaction', 'very_poor', '很差', '1', 1, True, True, None, '很差的满意度'),
        ('satisfaction', 'poor', '较差', '2', 2, True, True, None, '较差的满意度'),
        ('satisfaction', 'average', '一般', '3', 3, True, True, None, '一般的满意度'),
        ('satisfaction', 'good', '满意', '4', 4, True, True, None, '满意的满意度'),
        ('satisfaction', 'excellent', '非常满意', '5', 5, True, True, None, '非常满意的满意度')
    ]
    
    # 统计维度数据
    report_dimensions = [
        ('report_dimension', 'day', '按天', None, 1, True, True, None, '按天统计'),
        ('report_dimension', 'week', '按周', None, 2, True, True, None, '按周统计'),
        ('report_dimension', 'month', '按月', None, 3, True, True, None, '按月统计')
    ]
    
    # 合并所有数据
    all_data = (
        work_order_types + priority_levels + work_order_status + 
        material_categories + stock_status + usage_types + 
        user_roles + user_status + gender_options + 
        failure_reasons + satisfaction_levels + report_dimensions
    )
    
    # 批量插入数据
    cursor.executemany("""
        INSERT INTO system_dictionaries 
        (dict_type, dict_key, dict_label, dict_value, sort_order, is_active, is_system, parent_id, description)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, all_data)
    
    print(f"✅ 插入了 {len(all_data)} 条初始化数据")

def main():
    """主函数"""
    print("开始创建系统字典表...")
    
    if create_system_dictionaries_table():
        print("\n🎉 系统字典表创建成功！")
        print("\n下一步可以：")
        print("1. 创建字典管理API接口")
        print("2. 创建前端字典管理页面")
        print("3. 逐步替换前端硬编码数据")
    else:
        print("\n❌ 系统字典表创建失败")

if __name__ == '__main__':
    main()